﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Text;

namespace Leador.Bussiniess
{

    /// <summary>
    /// DataAccess 的摘要说明
    /// </summary>
    public class DataAccess
    {
        string connectionString;
        public DataAccess()
        {
            connectionString = "Data Source=192.168.10.160;Initial Catalog=qztl_test;Persist Security Info=True;User ID=sa; Password=leador!@#$%^";
        }

        public int GetRecordCount()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  count(ID) FROM LBS_Image");

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand(strSql.ToString(), connection);
                    return int.Parse(cmd.ExecuteScalar().ToString());
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public DataSet GetPagerData(int startRowIndex, int maximumRows)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ImageMainName desc) AS RowNumber,DeviceID,ImageMainName,ImageTime,ProjectID FROM LBS_Image ");

            strSql.Append(" ) _myResults WHERE RowNumber between ");
            strSql.Append(startRowIndex + 1);
            strSql.Append(" and ");
            strSql.Append(startRowIndex + maximumRows);

            DataSet ds = new DataSet();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    SqlDataAdapter command = new SqlDataAdapter(strSql.ToString(), connection);

                    command.Fill(ds);
                }
                catch (System.Data.SqlClient.SqlException ex)
                {

                    throw new Exception(ex.Message);
                }
            }
            return ds;
        }

        public int GetPageCount()
        {
            using (SqlConnection conn = new SqlConnection("Data Source=(local);User ID=sa;Password=Qwert!@#$%;Initial Catalog=Northwind"))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("select count(ProductID) from Products", conn);
                return (int)cmd.ExecuteScalar();
            }
        }
        public DataSet GetCustomList(int startRowIndex, int maximumRows)
        {
            using (SqlConnection conn = new SqlConnection("Data Source=(local);User ID=sa;Password=Qwert!@#$%;Initial Catalog=Northwind"))
            {
                DataSet ds = new DataSet();
                try
                {
                    conn.Open();
                    string strSql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ProductID) AS RowNumber, * FROM Products) Results WHERE RowNumber between " + (startRowIndex + 1) + " and " + (startRowIndex + maximumRows);
                    SqlDataAdapter da = new SqlDataAdapter(strSql, conn);
                    ds = new DataSet();
                    da.Fill(ds);
                }
                catch (Exception e)
                {
                    string a = e.ToString();
                }
                return ds;
            }
        }
    }
}